invalid join selectivity error from simple query

[paulfriedman]

query:

select 1 from

repository.bay_grid1000 k, reference.ca_region r

where

st_intersects(k.geom, r.geom);

error:

ERROR: invalid join selectivity: 1.000000 Error

ERROR: invalid join selectivity: 1.000000 SQL state: XX000

All tables were freshly analyzed/vacuumed and no luck.

[paulfriedman]

Using postgresql 9.3.1 on Ubuntu 12.04

[paulfriedman]

Workaround by

renaming ca_region to ca_region_old

and doing create table ca_region as select * from ca_region_old;

Query worked fine then.

[robe]

Paul,

Can you do a

SELECT postgis_full_version();

[paulfriedman]

"POSTGIS="2.1.1 r12113" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.7.8" LIBJSON="UNKNOWN" (core procs from "2.1.0 r11822" need upgrade) TOPOLOGY (topology procs from "2.1.0 r11822" need up (…)"

[robe]

Looks like you didn't run the upgrade step on your database

ALTER EXTENSION postgis UPDATE TO "2.1.1";

That might not solve problem but an important step anyway since some things fixed in this cycle need database upgrade

[paulfriedman]

Ran the upgrade statement and same result.

"POSTGIS="2.1.1 r12113" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY (topology procs from "2.1.0 r11822" need upgrade) RASTER"

[pramsey]

There's nothing wrong with a join selectivity of 1.0, so probably it's actually 1.000000001, but we're not seeing that in the print out. In that spirit, I've attached a patch, see if things work (they will). Maybe it should test for "within a tolerance" of 1.0 and error out if it exceeds that, rather than blindly flooring the value.

[pramsey]

Sooo…? better?

[paulfriedman]

I'm going to merge this and try it probably next week after the next db backup cycle. Thanks for your quick work!

[mwanner]

I run into a similar issue on mips (Debian/GNU) with postgis-2.1.1, where the calculated selectivity had 5 or more decimals, i.e. way above 1.0. As it's just an estimate, I simply applied the joinsel.patch mentioned above to the Debian package. I did not have time to investigate further, yet. But the original estimate certainly indicates something weird on that architecture.

[pramsey]

Committed to 2.1 branch and trunk as of r12222

[jimktrains]

I experienced this issue on a Windows 7 x64 Machine. robe2 suggested I reopen this ticket.

PostgreSQL 9.4.1

POSTGIS="2.1.5 r13152" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.1, released 2014/09/24 GDAL_DATA not found" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER

PostgreSQL 9.3.6

POSTGIS="2.1.3 r12547" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24 GDAL_DATA not found" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER"

SELECT * FROM

blocks AS b, neighborhood AS n

WHERE n.hood LIKE 'Central%'

AND ST_CONTAINS(n.geom,b.geom);

---

ERROR: invalid join selectivity: 22339560.000000 Error ERROR: invalid join selectivity: 22339560.000000 SQL state: XX000

+++++++++++++++++++++++++++++++++++++++++++++++++ HOWEVER on my home machine, with tables larger than the two I was helping my friend with, it works fine +++++++++++++++++++++++++++++++++++++++++++++++++

Ubuntu 14.04.1 geodata=# select version();

version

---

PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

(1 row)

geodata=# select postgis_full_version();NOTICE: Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed?

postgis_full_version

---

POSTGIS="2.1.3 r12547" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.0, released 2014/04/16" LIBXML="2.9.1" LIBJSON="UNKNOWN" (core procs from "2.1.2 r12389" need upgrade) RASTER (raster procs from "2.1.2 r12389" need upgrade)

(1 row)

geodata=# select count(*) from tiger2013.sldu inner join tiger2013.tabblock on st_contains(sldu.geom, tabblock.geom) where sldu.geoid = '42042';

count

---

25228

(1 row)

[robe]

Were you able to test the same exact tables on your machine that failed on your friends machine?

Would it be possible to provide a link to the tables in question that are failing? If it's confidential, you can send a link to my lr@… mail account.

Thanks, Regina

[jimktrains]

I was not able to test the exact same tables, and may not be back to their office for a bit. The ones I used were from the TIGER 2013 dataset for Pennsylvania (SLDU and TABBLOCK). Theirs was TABBLOCK for Allegheny County (FIPS=003) in Pennsylvnia (FIPS=42) and a file defining the neighborhoods in Pittsburgh (available here ​http://pittsburghpa.gov/dcp/gis/gis-data).

As my tables are much larger, containing all blocks in the state and not just our county, I wasn't sure exactly where to go to reproduce the problem. I could attempt to mimic their setup on a Windows 8 VM, but may not be able to do that for a few days. If you can think of a situation I can force my local install to force it to fail, I can give that a shot. (Tomorrow I will try with the exact data they were using, but on my machine).

Thanks, Jim

[robe]

I have a windows 7, so I'll try to replicate that with just that county of data

[pramsey]

Look at the patch. Immediately before the return, selectivity is cut off at 1. I tend to think the friend's computer is actually still running an old version. Please re-open if more info comes to light.

[jimktrains]

@pramsey I installed both versions that day, along with Postgres, so there were no older versions that could have crept in. First was POSTGIS="2.1.5 r13152" and when that failed I rolled back to what was on my (linux-based) laptop, POSTGIS="2.1.3 r12547".

I'll set up my Win8 VM and test this out. Maybe it was a "his-machine" issue somehow, but it was defiantly not due to a version of PostGIS that was previously installed.

I'll leave this closed until I can reproduce it in my VM.

[remic]

Hey, for the very first time, I was hit with the bug.

My query was select from A WHERE not exists (select 1 from where st_within(A.geom,B.geom))

I have very recent postgis : POSTGIS="2.2.0dev r12846" GEOS="3.5.0dev-CAPI-1.9.0 r0" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 2.0.0dev, released 2014/04/16" LIBXML="2.8.0" TOPOLOGY RASTER

I vacuumed to no effect, then I created a temp table select * from … Now I can't reproduce.

Cheers, Rémi-C

[remic]

Hit again, this time witha very simple query, very few data :

---

—or FROM bdtopo_topological.face AS f

, bdtopo_topological.node AS n

WHERE face_id = 0

AND ST_Intersects(n.geom , f.mbr ) = TRUE

---

I get "ERROR: invalid join selectivity: 157865408.000000" , same error for explain, explain analyse, and simple execution.

Tables are classical topological tables.

bdtopo_topological.node 2630 NULL POINT Z (2016.43602159963 20546.3348600308 0)

bdtopo_topological.face : 0 NULL

bdtopo_topological.edge_data : 890 2630 2630 -890 890 890 890 0 0 LINESTRING Z (2016.43602159963 20546.3348600308 0,1807.01948292597 20559.4718857428 0,1641.08811201422 20509.5039160932 0,1569.43592912051 20433.1377737986 0,1658.05836585747 20356.771631504 0,1841.90278249264 20298.3185349328 0,2018.20486408638 20319.0599562968 0,2064.40166621521 20385.0553879094 0,2059.68770681431 20486.8769109689 0,2016.43712931104 20546.3317239128 0)

Everything is vacuum analyzed. Select Postgis_full_version(); —POSTGIS="2.2.0dev r12846" GEOS="3.5.0dev-CAPI-1.9.0 r0" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 2.0.0dev, released 2014/04/16" LIBXML="2.8.0" TOPOLOGY RASTER

[remic]

Some more debug : The error comes from the && operator

---

SELECT * FROM bdtopo_topological.face AS f

, bdtopo_topological.node AS n

WHERE

n.geom && f.mbr

---

Using _st_intersects works fine (here : no result )

Another interesting fact : I get the same error with or without the geometric (GIST) indexes.

The error seems to come from [here](​http://doxygen.postgresql.org/plancat_8c_source.html) , line 1460 Cheers, Rémi-C

[pramsey]

Can you try that patch yourself, remi? Just guard against ! isfinite() with teh default selectivity macro as a return value?

[pramsey]

I guarded against crazier outputs in r13600

[remic]

Hmm this feels good ! Thanks Paul, I'll try it tonight, I don't have any way to know for sure it works, but at least it can't hurt.

Cheers, Rémi-C

[pramsey]

I have no further action to take until I can get a reproduction case.